package com.uit.gis.dao;

import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import oracle.spatial.geometry.JGeometry;
import oracle.sql.STRUCT;

import com.uit.gis.pojo.Shape;
import com.uit.gis.pojo.ShapeFactory;
import com.uit.gis.util.DatabaseDefine.TABLE_STATUS;
import com.uit.gis.util.OracleDataAccessHelper;
import com.uit.gis.util.ShapeName;
import com.uit.gis.util.Util;

@SuppressWarnings("deprecation")
public class ShapeDAO {
	static final String TABLE_NAME = "INTERGRATEGPO";
	static final String COL_ID = "ID";
	static final String COL_NAME = "NAME";
	static final String COL_DESCRIPTION = "DES";
	static final String COL_SHAPE = "SHAPE";

	static final String COSTRAINT_KEY = "GPO_KEY";
	static final String SEQUENCE_GIS = "GPO_SEG";
	static final String TRIGGER_NAME = "GPO_TRIGGER";

	/**
	 * Create table if
	 * 
	 * @param databaseHelper
	 * @return TABLE_STATUS
	 */
	public TABLE_STATUS createTable(OracleDataAccessHelper databaseHelper) {

		TABLE_STATUS result = TABLE_STATUS.CANT_CREATE;
		String createStr = String.format("CREATE TABLE %s ( %s NUMBER(10) "
				+ "NOT NULL, %s VARCHAR2(50)  NOT NULL, "
				+ "%s VARCHAR2(100) NOT NULL,SHAPE MDSYS.SDO_GEOMETRY)",
				TABLE_NAME, COL_ID, COL_NAME, COL_DESCRIPTION);

		String addFKey = String.format(
				"ALTER TABLE %s ADD ( CONSTRAINT  %s PRIMARY KEY (%s))",
				TABLE_NAME, COSTRAINT_KEY, COL_ID);
		String dropSequence = String.format("DROP SEQUENCE %s", SEQUENCE_GIS);
		String sequence = String.format("CREATE SEQUENCE %s", SEQUENCE_GIS);

		String trigger = String
				.format("CREATE OR REPLACE TRIGGER %s BEFORE INSERT ON "
						+ "%s FOR EACH ROW BEGIN SELECT %s.NEXTVAL INTO:new.%s "
						+ "FROM dual; END;", TRIGGER_NAME, TABLE_NAME,
						SEQUENCE_GIS, COL_ID);
		// String trigger = "CREATE OR REPLACE TRIGGER GPO_TRIGGER "
		// + "BEFORE INSERT ON INTERGRATEGPO "
		// + "FOR EACH ROW "
		// + "BEGIN "
		// + "SELECT GPO_SEG.NEXTVAL "
		// + "INTO:new.id "
		// + "FROM   dual; "
		// + "END;";

		int n = databaseHelper.executeCreate(createStr);
		n = databaseHelper.executeCreate(addFKey);
		n = databaseHelper.executeCreate(dropSequence);
		n = databaseHelper.executeCreate(sequence);
		n = databaseHelper.executeCreate(trigger);

		if (n > 0) {
			result = TABLE_STATUS.CREATE_SUCCESSFUL;
		}
		return result;
	}

	public boolean tableExisting(OracleDataAccessHelper dataAccessHelper) {
		boolean result = false;
		DatabaseMetaData dbm;
		try {
			dbm = dataAccessHelper.getConnection().getMetaData();
			ResultSet tables = dbm.getTables(null, null,
					TABLE_NAME.toUpperCase(), null);
			if (tables.next()) {
				// Table exists
				result = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			result = false;
		}
		return result;
	}

	// sdo_elem_info_array (1,2,1, 5,2,1)
	// (1,2,1): 1: index , 2- 1: point type [point one]
	// (5,2,1): 5: index, 2-1: point type [point two]
	// 2-1 : Line string whose vertices are connected by straight line segments.
	public boolean saveShape(OracleDataAccessHelper dataAccessHelper,
			Shape shape, StringBuffer elemInfo, StringBuffer ordiateArray) {
		boolean result = false;
		String sql = String
				.format("INSERT INTO %s values(1,'%s','%s',"
						+ "SDO_GEOMETRY(%s,NULL,NULL,SDO_ELEM_INFO_ARRAY(%s),SDO_ORDINATE_ARRAY(%s)))",
						TABLE_NAME, shape.getName(),
						"Description -- " + shape.getName(),
						shape.getSpatialType(), elemInfo, ordiateArray);
		int n = dataAccessHelper.executeUpdate(sql);
		if (n == 1) {
			result = true;
		}
		return result;
	}

	public ArrayList<Shape> getAllTable(OracleDataAccessHelper dataAccessHelper) {

		ArrayList<Shape> shapes = new ArrayList<Shape>();
		if (!dataAccessHelper.isConnected())
			return shapes;
		try {
			String sql = String.format("SELECT * FROM %s", TABLE_NAME);
			ResultSet rs = dataAccessHelper.executeQuery(sql);

			while (rs.next()) {
				ShapeName shapeName = ShapeName.LINE;
				shapeName = shapeName.getShape(rs.getString(COL_NAME));
				Shape shape = ShapeFactory.getShape(shapeName);
				shape.setName(shapeName);
				shape.setId(rs.getInt(COL_ID));
				shape.setDescription(rs.getString(COL_DESCRIPTION));
				STRUCT st = (oracle.sql.STRUCT) rs.getObject(COL_SHAPE);
				JGeometry j_geom = JGeometry.load(st);
				shape.setOrdinateInfor(Util.getOrdinateElement(j_geom
						.getOrdinatesArray()));
				shape.setElementInfor(Util.getArrayElemnt(j_geom.getElemInfo()));
				shapes.add(shape);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return shapes;
	}

}
